import sqlite3
from functools import wraps
from itertools import repeat, chain
from datetime import datetime

def getdb(name):
    """
    connect to or create the database given by name.
    """
    return sqlite3.connect(name)

def commit(fn):
    """
    auto commit after function completes, does not commit if
    function raises an exception.
    """
    @wraps(fn)
    def committed(db, *args):
        fn(db, *args)
        db.commit()
    return committed

@commit
def update(dbconn, tablename, dates, other):
    "part of the history model. ensure old records are marked as such."
    template = ("update %s "
                "set latest=0 "
                "where datetime=? and created<? and latest=1 "
                "and other!=?") % tablename
    now = datetime.now()
    dbconn.executemany(template, zip(dates, repeat(now), other))

@commit
def insert(dbconn, tablename, dates, other):
    template = ("insert into %s(datetime, other, latest, created)"
                 "values (?, ?, ?, ?)") % tablename

    now = datetime.now()
    dbconn.executemany(template, zip(dates, other, repeat(1), repeat(now)))

@commit
def create(dbconn, tablename, columndefs):
    "columndefs - a sequence mapping the columnname and type"
    assert len(columndefs) == 2, "cannot handle more than two columns."

    columns = chain((('pk', 'INTEGER PRIMARY KEY'),), columndefs,
                (('latest', 'INTEGER'), ('created','TIMESTAMP')))

    dbconn.execute("create table %s (" % tablename +
            ",".join(("%s %s" % item) for item in columns) + ")")

@commit
def store(dbconn, tablename, dates, other):
    """
    store the column of dates and other in tablename at database dbconn.
    """
    try:
        # mark superseeded entries as old.
        update(dbconn, tablename, dates, other)
        # insert all new entries.
        insert(dbconn, tablename, dates, other)
    except sqlite3.Error:
        create(dbconn, tablename, (('datetime','TIMESTAMP'),('other','TEXT')))
        insert(dbconn, tablename, dates, other)

def save(dates, other, tblname=None, dbname=None):
    """
    connect to a default database and save the results in it.

    dates and other are the columns to save (1D sequences).
    tblname is the descriptive name given to the data to use as tablename.
    dbname is the name of the file to save the results to.
    """
    if not dbname:
        dbname = 'predispatch.db'
    if not tblname:
        tblname = 'default'
    db = getdb(dbname)
    store(db, tblname, dates, other)

def query(dbconn, tablename):
    """
    return all the latest records found at tablename.
    """
    return dbconn.execute('select * from %s where latest=1' % tablename)

